package com.guojian.jdbc.sql;

import java.lang.reflect.Field;
import java.sql.Date;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.lang.StringUtils;
import org.hibernate.id.UUIDHexGenerator;
import org.springframework.util.Assert;
import org.springframework.util.LinkedCaseInsensitiveMap;
import org.springframework.util.ObjectUtils;

import com.google.common.collect.Lists;
import com.guojian.common.id.UuidUtils;
import com.guojian.common.reflect.ReflectUtils;
import com.guojian.common.utils.StringUtilsx;
import com.guojian.jdbc.Constants;
import com.guojian.jdbc.annotation.SqlExp;

import lombok.extern.slf4j.Slf4j;

/**
 * 传入T object,自动获取object属性值来生成 sql
 * @ID 指定delete,update 的where条件
 * @Table 表名称
 * @Column 列名称
 * @SqlExp 条件表达式 + 别名
 * @author GuoJian
 */
@Slf4j
public class EntityInfo<T> {

	private Class<?> tClass;
	private Field[] fields;
	private Table tableAnnotation;
	private String tableName;
	private T object;
	private SqlExp sqlExpAnnotation;

	//不区分大小写 map.get("AA") == map.get("aa")
	private Map<String, Field> generatedColumnNameFieldMap = new LinkedCaseInsensitiveMap<>(0);

	public EntityInfo(T object) {
		this.tClass = object.getClass();
		this.fields = ReflectUtils.getDeclaredFields(tClass);
		this.tableAnnotation = this.gettClass().getAnnotation(Table.class);
		this.setTableName();
		this.object = object;
		this.sqlExpAnnotation = this.gettClass().getAnnotation(SqlExp.class);
	}

	/**
	 * 
	 * 1.没有写条件表达式则默认"="
	 * 2.只要对象的属性值不为空,则自动纳入where 之后作为条件
	 * 3.列名自动转成带"_"的命名方式 beginDate -> begin_date
	 * @param sqlParams
	 * @return
	 */
	public String buildSelectSql(List<Object> sqlParams) {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select * from " + this.getTableName());

		List<String> columnNames = Lists.newArrayList();
		List<String> sqlExpList = Lists.newArrayList();

		for (Field field : this.getFields()) {
			Object columnValue = this.getColumnValue(field);
			if (columnValue == null) {
				continue;
			}
			sqlParams.add(columnValue);
			SqlExp sqlExp = field.getAnnotation(SqlExp.class);
			if (sqlExp == null) {
				sqlExpList.add(SqlExpression.EQUAL.getValue() + " ?");
			} else {
				sqlExpList.add(sqlExp.exp().getValue() + " ?");
			}
			String columnName = this.getColumnName(field);
			columnNames.add(columnName);
			//SqlBuilder.appendSelectWhereString(sqlBuilder, sqlParams, this.getColumnName(field), sqlExp == null ? SqlExpression.EQUAL.getValue() : sqlExp.exp().getValue(), columnValue);
		}
		String whereString = StringUtilsx.collectionToDelimitedString(columnNames, " ", "and ", sqlExpList);
		if (StringUtils.isBlank(whereString)) {
			return sqlBuilder.toString();
		} else {
			sqlBuilder.append(" where 1=1 ");
			sqlBuilder.append(whereString);
		}
		//去除1=1 and
		return SqlBuilder.deleteAnd(sqlBuilder.toString());
	}

	/**
	 * 
	 * GeneratedValue不指定则采用数据库默认的方式生成主键,auto 采用程序生成主键
	 * @param sqlParams
	 * @return
	 */
	public String buildInsertSql(List<Object> sqlParams) {
		Assert.notNull(sqlParams, "参数列表 sqlParams 不能为空!");

		List<String> columnNames = Lists.newArrayList();
		List<String> columnValueString = Lists.newArrayList();

		for (int i = 0; i < this.getFields().length; i++) {
			Field field = getFields()[i];
			GeneratedValue generatedValue = field.getAnnotation(GeneratedValue.class);
			Object columnValue = this.getColumnValue(field);
			String columnName = this.getColumnName(field);
			if (generatedValue != null) {
				getGeneratedColumnNameFieldMap().put(columnName, field);
				if (GenerationType.SEQUENCE == generatedValue.strategy()) {
					String generator = generatedValue.generator();
					Assert.hasText(generator, " 已配置 GenerationType = SEQUENCE, 必须指定序列名称 generator='seqNamexx' ");
					columnNames.add(this.getColumnName(field));
					columnValueString.add(generator + ".nextval");
				} else if (GenerationType.AUTO == generatedValue.strategy()) {
					columnNames.add(this.getColumnName(field));
					columnValueString.add("?");
					sqlParams.add(UuidUtils.generateShortUuidWithTime());
				} else if (GenerationType.IDENTITY == generatedValue.strategy()) {
					log.debug("已配置 GenerationType = IDENTITY, 将采用数据库自身策略生成主键");
				}
				continue;
			}

			if (columnValue == null) {
				continue;
			}
			columnNames.add(this.getColumnName(field));
			columnValueString.add("?");
			sqlParams.add(columnValue);
		}

		if (hasGeneratedProperty()) {
			String returnColumnNames = StringUtilsx.collectionToCommaDelimitedString(getGeneratedColumnNameFieldMap().keySet());
			log.debug("InsertSql 已设置 GeneratedValue = {}, 将返回 {} 对应的值", returnColumnNames, returnColumnNames);
		} else {
			log.debug("InsertSql 未设置 GeneratedValue, 将不返回任何已插入的数据");
		}

		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" insert into ").append(this.getTableName()).append("(");
		sqlBuilder.append(StringUtilsx.collectionToCommaDelimitedString(columnNames)).append(") values (");
		sqlBuilder.append(StringUtilsx.collectionToCommaDelimitedString(columnValueString)).append(")");
		return sqlBuilder.toString();
	}

	/**
	 * 非空的属性值自动修改,需要用@ID来指定where条件
	 * @param sqlParams
	 * @return
	 */
	public String buildUpdateSql(List<Object> sqlParams) {
		Assert.notNull(sqlParams, "参数列表 sqlParams 不能为空!");
		List<String> columnSetNames = Lists.newArrayList();
		List<String> columnWhereNames = Lists.newArrayList();
		List<Object> sqlWhereParams = Lists.newArrayList();

		for (Field field : this.getFields()) {

			String columnName = this.getColumnName(field);
			Object columnValue = this.getColumnValue(field);
			if (columnValue == null) {
				continue;
			}

			Id id = field.getAnnotation(Id.class);
			if (id != null) {
				columnWhereNames.add(columnName);
				sqlWhereParams.add(columnValue);
			} else {
				columnSetNames.add(this.getColumnName(field));
				sqlParams.add(columnValue);
			}
		}

		sqlParams.addAll(sqlWhereParams);
		Assert.notEmpty(columnWhereNames, "ColumnWhereNames must not be empty, buildUpdateSql 必须带上where条件 ");

		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" update ").append(this.getTableName());
		sqlBuilder.append(" set ").append(StringUtilsx.collectionToDelimitedString(columnSetNames, ",", " ", " = ?")).append(" where ").append(Constants.Sql.TRUE_EXP_SQL);
		sqlBuilder.append(StringUtilsx.collectionToDelimitedString(columnWhereNames, " ", "and ", " = ?"));

		return SqlBuilder.deleteAnd(sqlBuilder.toString());
	}

	/**
	 * 需要用ID来指定where条件
	 * @param sqlParams
	 * @return
	 */
	public String buildDeleteSql(List<Object> sqlParams) {
		List<String> columnNames = Lists.newArrayList();

		for (Field field : this.getFields()) {
			String columnName = this.getColumnName(field);
			Object columnValue = this.getColumnValue(field);
			if (columnValue == null) {
				continue;
			}

			Id id = field.getAnnotation(Id.class);
			if (id != null) {
				columnNames.add(columnName);
				sqlParams.add(columnValue);
			}
		}

		Assert.notEmpty(columnNames, "buildDeleteSql where条件不能为空");

		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" delete from  ").append(this.getTableName()).append(" where ").append(Constants.Sql.TRUE_EXP_SQL);
		sqlBuilder.append(StringUtilsx.collectionToDelimitedString(columnNames, " ", "and ", " = ?"));
		return SqlBuilder.deleteAnd(sqlBuilder.toString());
	}

	public void setTableName() {
		String tableNameTemp = StringUtilsx.toLowerCaseOfUnderscore(this.gettClass().getSimpleName());
		if (this.getTableAnnotation() != null && StringUtils.isNotBlank(this.getTableAnnotation().name())) {
			tableNameTemp = this.getTableAnnotation().name();
		}
		this.tableName = tableNameTemp;
	}

	public Object getColumnValue(Field field) {
		Assert.notNull(field, "getColumnValue field must be not null");
		ReflectUtils.makeAccessible(field);
		try {
			return convertColumnValue(field, field.get(this.getObject()));
		} catch (IllegalArgumentException | IllegalAccessException e1) {
			throw new RuntimeException(" getColumnValue 异常, " + e1.getMessage());
		}
	}

	/**
	 * 
	 * @param field
	 * @return
	 */
	public String getColumnName(Field field) {
		Assert.notNull(field, "getColumnName field must be not null");
		Column column = field.getAnnotation(Column.class);
		if (column != null && StringUtils.isNotBlank(column.name())) {
			return column.name();
		}
		return StringUtilsx.toLowerCaseOfUnderscore(field.getName());
	}

	public String getColumnNameWithAlias(Field field) {
		Assert.notNull(field, "getColumnNameWithAlias field must be not null");

		String tableAlias = StringUtils.EMPTY;
		SqlExp sqlExpTable = this.getTableSqlExp();
		if (sqlExpTable != null) {
			tableAlias = sqlExpTable.alias();
		}

		String fieldAlias = StringUtils.EMPTY;
		SqlExp sqlExp = this.getFieldSqlExp(field);
		if (sqlExp != null) {
			fieldAlias = sqlExp.alias();
		}

		//fieldAlias is null and replace with tableAlias
		if (StringUtilsx.isBlank(fieldAlias)) {
			fieldAlias = tableAlias;
		}

		String columnName = this.getColumnName(field);

		if (StringUtilsx.hasText(fieldAlias)) {
			columnName = fieldAlias + "." + columnName + " ";
		}
		return columnName;
	}

	public SqlExp getFieldSqlExp(Field field) {
		Assert.notNull(field, "getSqlExp field must be not null");
		return field.getAnnotation(SqlExp.class);
	}

	public Object convertColumnValue(Field field, Object columnValue) {
		if (columnValue == null)
			return columnValue;

		Temporal dateType = field.getAnnotation(Temporal.class);
		if (dateType != null) {
			if (TemporalType.DATE == dateType.value()) {
				return Date.valueOf(ObjectUtils.getDisplayString(columnValue));
			} else if (TemporalType.TIMESTAMP == dateType.value()) {
				return Timestamp.valueOf(ObjectUtils.getDisplayString(columnValue));
			}
		}
		return columnValue;
	}

	public boolean hasGeneratedProperty() {
		return getGeneratedColumnNameFieldMap().size() > 0;
	}

	public Class<?> gettClass() {
		return tClass;
	}

	public Field[] getFields() {
		return fields;
	}

	public Table getTableAnnotation() {
		return tableAnnotation;
	}

	public String getTableName() {
		return tableName;
	}

	public T getObject() {
		return object;
	}

	public SqlExp getTableSqlExp() {
		return sqlExpAnnotation;
	}

	public Map<String, Field> getGeneratedColumnNameFieldMap() {
		return generatedColumnNameFieldMap;
	}

	public static void main(String[] args) {
		UUIDHexGenerator uuid = new UUIDHexGenerator();
		String uid = uuid.generate(null, null).toString();
		System.out.println(DigestUtils.md5Hex(uid).substring(8, 24));
		System.out.println(Integer.parseInt("6d", 16) % 32);
	}

}
